const query = require('../../dbConfig/index')
const XLSX = require('xlsx'); 
const get = async (ctx,next)=>{
	const body = ctx.request.body
	console.log(body)
	const offset = (body.pageNumber - 1) * Number(body.pageSize);  
	let sql = 'SELECT * FROM achievement';  
	let params = [];  
	// 如果传了条件 就拼接sql

	if (body.classesName) {  
		sql += sql.includes('WHERE')? ' and classes_id=?':' WHERE classes_id=?';  
		params.push(body.classesName);  
	}else if(ctx.state.power!=1&&ctx.state.classes){
		let str = ctx.state.classes.split(",").map(num => parseInt(num))
		sql += sql.includes('WHERE')? ' and classes_id IN (?)':' WHERE classes_id IN (?)';  
		params.push(str); 
	}
	if (body.speciality_id) {  
		sql +=sql.includes('WHERE')? 'and speciality=?': ' WHERE speciality=?';  
		params.push(body.speciality_id);  
	}  
	  sql += ' LIMIT ? OFFSET ?';  
	  params.push(Number(body.pageSize));  
	  params.push(offset);
	const res = await query(sql,params)
	const total = await query(`SELECT COUNT(*) as total FROM student`)
	if(res.length>0){
		ctx.body={code:200,data:{records:res,total:total[0].total},msg:'成功'}
		next()
	}else{
		ctx.body={code:0,data:null,msg:'暂无试卷数据'}
	}
}

const getItemData = async (ctx,next)=>{
	const body = ctx.request.body
	let sql = `SELECT t.*,a.*,s.* FROM transcript t LEFT JOIN achievement a ON t.achievement_id=a.id LEFT JOIN speciality s ON s.speciality_id=a.speciality where t.student_id='${body.student_id}'`;  
	const res = await query(sql)
	if(res.length>0){
		ctx.body={code:200,data:res,msg:'成功'}
		next()
	}else{
		ctx.body={code:0,data:null,msg:'没有成绩数据'}
	}
}

const updata = async (ctx,next)=>{
	const body = ctx.request.body
	await query(`UPDATE achievement SET paper_name = ?,speciality=?,teacher_id=?,startTime=?,classes_id=? WHERE id='${body.id}'`,[body.paper_name,body.speciality,body.teacher_id,body.startTime,body.classes_id])
	ctx.body={code:200,data:null,msg:'成功'}
	next()
	
}
const add = async (ctx,next)=>{
	const body = ctx.request.body
	console.log(body)
	await query(`INSERT INTO achievement (paper_name,speciality,teacher_id,startTime,classes_id) VALUES (?,?,?,?,?)`,[body.paper_name,body.speciality,body.teacher_id,body.startTime,body.classes_id])
	ctx.body={code:200,data:null,msg:'成功'}
	next()
}
const remove = async (ctx,next)=>{
	const body = ctx.request.body
	await query(`DELETE FROM achievement WHERE id=${body.id}`)
	ctx.body={code:200,data:null,msg:'成功'}
	next()
}

const addScore = async (ctx,next)=>{
	const body = ctx.request.body
	console.log(body)
	let values = body.map(item => [item.id,item.student_id, item.score]);
	await query(`INSERT INTO transcript (achievement_id,student_id,score) VALUES ?`,[values])
	ctx.body={code:200,data:null,msg:'成功'}
	next()
}

const getScore = async (ctx,next)=>{
	const body = ctx.request.body
	console.log(body)
	const res = await query(`SELECT * FROM transcript where achievement_id='${body.achievement_id}'`)
	ctx.body={code:200,data:res,msg:'成功'}
	next()
}

const exportFile = async (ctx,next)=>{
	const res = await query(`select * FROM student`)
	const speciality = await query(`select * FROM speciality`)
	const stateMap = {0:'在校',1:'放假',2:'请假',3:'就业',4:'实习',5:'退学',6:'停课',7:'休学'}
	const obj = {}
	speciality.forEach(item=>{
		obj[item.speciality_id] = item.speciality_name
	})
	res.forEach(element => {
		element.specialty = obj[element.specialty]
		element.student_sex = Number(element.student_sex)?'女':'男'
		element.school_status = stateMap[element.school_status]
	});
	const wb = XLSX.utils.book_new();  
	  const ws = XLSX.utils.json_to_sheet(res);  
	  const headers = ['序号', '学号', '姓名','性别','年龄','学制','民族','出生年月','班级','入学时间','状态','退学时间','地址','毕业时间','手机号','专业'];
	  const col = ['A1','B1','C1','D1','E1','F1','G1','H1','I1','J1','K1','L1','M1','N1','O1','P1']
	  for(let i=0;i<col.length;i++){
		ws[col[i]] = { t: 's', v: headers[i] };
	  }
	  XLSX.utils.book_append_sheet(wb, ws, 'Sheet1');
	  const wbout = XLSX.write(wb, { bookType: 'xlsx', type: 'buffer' }); 
	  ctx.set('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');  
  	  ctx.set('Content-Disposition', 'attachment; filename="data.xlsx"');
	  ctx.body = wbout;
	next()
}
const importFile = async (ctx,next)=>{
	// console.log(ctx.request.files.studentsExcel.filepath)
	const filePath = ctx.request.files.studentsExcel.filepath; 
	const workbook = XLSX.readFile(filePath);
	const worksheet = workbook.Sheets[workbook.SheetNames[0]]; 
	const jsonData = XLSX.utils.sheet_to_json(worksheet, { header: 2 }); 
	jsonData.shift()
	for (let i = 0; i < jsonData.length; i++) {
		delete jsonData[i]['学生学籍信息']
		delete jsonData[i]['_14']
	}
	console.log(jsonData,1)
	// TODO 可以用数组试试
	let values = ''
	for(let i=0;i<jsonData.length;i++){
			// values+=`(${jsonData[i][""]},${jsonData[i]['_1']},${jsonData[i]['_2']},${jsonData[i]['_3']},${jsonData[i]['_4']},${jsonData[i]['_5']},${jsonData[i]['_6']},${jsonData[i]['_7']},${jsonData[i]['_8']},${jsonData[i]['_9']},${jsonData[i]['_10']||null},${jsonData[i]['_11']},${jsonData[i]['_12']},${jsonData[i]['_13']},${jsonData[i]['__EMPTY']})`
			await query(`INSERT INTO student (student_num,student_name,student_sex,student_age,school_year,student_nation,student_birthday,classes_id,start_school_time,school_status,drop_time,address,graduate_time,student_tel,specialty) VALUES ('${jsonData[i][""]}','${jsonData[i]['_1']}','${jsonData[i]['_2']}','${jsonData[i]['_3']}','${jsonData[i]['_4']}','${jsonData[i]['_5']}','${jsonData[i]['_6']}','${jsonData[i]['_7']}','${jsonData[i]['_8']}','${jsonData[i]['_9']}',${handelEmpty(jsonData[i]['_10'])},'${jsonData[i]['_11']}','${jsonData[i]['_12']}','${jsonData[i]['_13']}','${jsonData[i]['__EMPTY']}')`)
		}
	function handelEmpty(d){
		if(d){
			return "'"+d+"'"
		}else{
			return null
		}
	}
	
	ctx.body={code:200,data:null,msg:'成功'}
	next()
}
module.exports = {
	get,getItemData,
	add,updata,remove,addScore,getScore,
	exportFile,
	importFile
}